package com.sp2p.service;

import com.shove.Convert;
import com.shove.base.BaseService;
import com.shove.data.DataException;
import com.shove.data.DataSet;
import com.shove.data.dao.MySQL;
import com.shove.util.BeanMapUtils;
import com.shove.util.UtilDate;
import com.shove.vo.PageBean;
import com.sp2p.constants.IConstants;
import com.sp2p.dao.MyHomeDao;
import com.sp2p.dao.OperationLogDao;
import com.sp2p.dao.UserDao;
import com.sp2p.database.Dao.Procedures;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @ClassName: MyHomeService.java
 * @Author: 
 * @Date: 2013-5-01
 * @Copyright: 
 * @Version: V2.01
 * @Descrb: 我的账户服务层
 */
public class MyHomeService extends BaseService {

	public static Log log = LogFactory.getLog(MyHomeService.class);

	private MyHomeDao myHomeDao;
	private UserDao userDao;
	private OperationLogDao operationLogDao;

	public MyHomeDao getMyHomeDao() {
		return myHomeDao;
	}

	public void setMyHomeDao(MyHomeDao myHomeDao) {
		this.myHomeDao = myHomeDao;
	}

	public UserDao getUserDao() {
		return userDao;
	}

	public void setUserDao(UserDao userDao) {
		this.userDao = userDao;
	}

	public OperationLogDao getOperationLogDao() {
		return operationLogDao;
	}

	public void setOperationLogDao(OperationLogDao operationLogDao) {
		this.operationLogDao = operationLogDao;
	}
	//余额宝投资记录
	public void queryInvestYebRecord(Long id,PageBean pageBean) throws SQLException,DataException {
		StringBuffer condition = new StringBuffer();
		condition.append(" and userId =" + id);
		Connection conn = connectionManager.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_yeb_invest ", " * ", "",
					condition.toString());

		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	public void queryInvestYebRecordDetail(Long id,PageBean pageBean) throws SQLException,DataException {
		StringBuffer condition = new StringBuffer();
		condition.append(" and userId =" + id);
		Connection conn = connectionManager.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_yeb_invest_detail ", " * ", " order by  investTime desc ",
					condition.toString());

		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}

	public Map<String, String> queryInvestYebRecordDetail(long id,long userId)
			throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = connectionManager.getConnection();
		try {
			command.append(" select * ");
			command.append(" from v_t_yeb_invest_detail ");
			command.append(" where id = " + id);
			command.append(" and userId = " + userId);
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	//产品投资记录
	public void queryInvestProductRecord(int borrowProduct,int status, Long id,
			PageBean pageBean) throws SQLException,DataException {
		StringBuffer condition = new StringBuffer();
		if (status > 0) {
			condition.append(" and borrowStatus =" + status);
		}else if(status == 0){
			condition.append(" and borrowStatus in(2,3,4)");
		}
		
		if(borrowProduct > 0){
			condition.append(" and borrowProduct =  " + borrowProduct );
		}else{
			return;
		}
		
		condition.append(" and investor =" + id + " order by investTime desc ");
		Connection conn = connectionManager.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_borrow_investrecord ", " * ", "",
					condition.toString());

		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	//产品投资统计
	public List<Map<String,Object>> queryInvestProductRecordTotal(Long id) throws SQLException,DataException {
		Connection conn = MySQL.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT borrowProduct, count(borrowProduct) numTotal ");
		sql.append("FROM v_t_borrow_investrecord WHERE 1 = 1 ");
		sql.append("AND investor = " + id + " GROUP BY borrowProduct");
		try {
			DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	public void queryInvestRecordDetail(int investId, Long id,
			PageBean pageBean) throws SQLException,DataException {
		StringBuffer condition = new StringBuffer();
		if(investId > 0){
			condition.append(" and invest_id =  " + investId );
		}
		
		condition.append(" and userId =" + id);
		Connection conn = connectionManager.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_forpayment_h ", " * ", "order by repayDate",
					condition.toString());

		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	public Map<String, String> queryInvestById(long investId, long userId)
			throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = connectionManager.getConnection();
		try {
			command.append(" select * ");
			command.append(" from v_t_borrow_investrecord ");
			command.append(" where id = " + investId);
			command.append(" and investor = " + userId);
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public Map<String, String> queryYebInvestById(long investId, long userId)
	throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = connectionManager.getConnection();
		try {
			command.append(" select  date_format(investTime,' %Y 年 %m 月 %d 日') as fmtinvestTime,v_t_yeb_invest_detail.* ");
			command.append(" from v_t_yeb_invest_detail ");
			command.append(" where investId = " + investId);
			command.append(" and userid = " + userId);
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
		}
	
	public Map<String, String> queryAccountStatisInfo(long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getUserAmountSumary(conn, ds, outParameterValues,
					userId);
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return map;
	}
	
	public Map<String, String> getWeightingRate(long userId) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getWeightingRate(conn, ds, outParameterValues,
					userId);
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return map;
	}
	

	// 余额宝产品7日总收益
	public List<Map<String, String>> queryYeb7daysTotalRevenue(long userId,
			int yebId) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" select DATE_FORMAT(liquidationTime,'%m.%d') as rq,InterestSum from t_yeb_invest_history ");
			command.append(" where userid=" + userId);
			command.append(" and investId IN (select investid from `t_yeb_invest` where id=" + yebId);
			command.append("  UNION ALL select investid from `t_yeb_invest` where id = " +
					"(SELECT oriYebInvestId  from `t_yeb_invest` where id=" + yebId + "))");
			command.append(" AND datediff(curdate(), liquidationTime) < 7 ");
			command.append(" ORDER by liquidationTime ASC LIMIT 0,7;");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			command = null;
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: 
	 * @Param: MyHomeService
	 * @Author:
	 * @Date:
	 * @Return:
	 * @Descb: 计算 加权收益率
	 * @Throws:
	 */
	public Map<String, String> queryWeightingRate(long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = myHomeDao.queryWeightingRate(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return map;
	}
	
	public Map<String,String> queryRepayDate(String investor) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" select investor,min(repayDate) as minRepayDate,max(repayDate) as maxRepayDate ");
			command.append(" from v_t_forpayment_h ");
			command.append(" where investor = '" +investor + "'");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public List<Map<String,String>> queryRepaySituation(String investor,int borrowProduct) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" SELECT repayDate, sum(recivedPrincipal) AS repayPrincipal, sum(recivedInterest) AS repayInterest, sum(forTotalSum) AS forTotalSum, count(id) AS loanCounts ");
			command.append(" from v_t_forpayment_h ");
			command.append(" where investor = '" +investor + "' ");
			command.append(" and borrowProduct = " +borrowProduct);
			command.append(" and repayStatus = 1");
			command.append(" and repayDate >= curdate() group by repayDate ");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			command = null;
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @MethodName: queryBorrowFinishByCondition
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-18 下午10:26:03
	 * @Return:
	 * @Descb: 查询已发布的借款列表
	 * @Throws:
	 */
	@SuppressWarnings("unchecked")
	public void queryBorrowFinishByCondition(String title,
			String publishTimeStart, String publishTimeEnd,
			String borrowStatus, long userId, PageBean pageBean)
			throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(title)) {
			condition.append(" and borrowTitle  LIKE CONCAT('%','"
					+ StringEscapeUtils.escapeSql(title.trim()) + "','%')");
		}
		if (StringUtils.isNotBlank(publishTimeStart)) {
			condition.append(" and publishTime >'"
					+ StringEscapeUtils.escapeSql(publishTimeStart.trim())
					+ "'");
		}
		if (StringUtils.isNotBlank(publishTimeEnd)) {
			condition.append(" and publishTime <'"
					+ StringEscapeUtils.escapeSql(publishTimeEnd.trim()) + "'");
		}
		if (StringUtils.isNotBlank(borrowStatus)) {
			String idStr = StringEscapeUtils
					.escapeSql("'" + borrowStatus + "'");
			String idSQL = "-2";
			idStr = idStr.replaceAll("'", "");
			String[] array = idStr.split(",");
			for (int n = 0; n <= array.length - 1; n++) {
				idSQL += "," + array[n];
			}
			condition.append(" and borrowStatus in(" + idSQL + ")");
		}
		condition.append(" and userId =" + userId);
		System.out.println(condition.toString());
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_borrow_publish", resultFeilds,
					" group by id order by id desc", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * @MethodName: queryHomeInfo
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-22 下午07:09:41
	 * @Return:
	 * @Descb: 查询统计后的个人信息
	 * @Throws:
	 */
	public Map<String, String> queryHomeInfo(long userId) throws SQLException,
			DataException {
		Connection conn = MySQL.getConnection();
		String startTime = UtilDate.getMonthFirstDay();
		String endTime = UtilDate.getMonthLastDay();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getUserInfo(conn, ds, outParameterValues, userId,
					startTime, endTime);

			conn.commit();

			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
			// System.out.println(map);
		} catch (Exception e) {
			e.printStackTrace();
			conn.rollback();
		} finally {
			conn.close();
		}

		return map;
	}

	

	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: queryLoanStatis
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-26 下午02:58:07
	 * @Return:
	 * @Descb: 查询借款统计
	 * @Throws:
	 */
	public Map<String, String> queryLoanStatis(Long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getBorrowStatis(conn, ds, outParameterValues, id,
					new Date());
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: queryLoanStatis
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-26 下午02:58:07
	 * @Return:
	 * @Descb: 查询借款人借款统计
	 * @Throws:
	 */
	public Map<String, String> queryBorrowPerStatis(Long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getBorrowPersonStatis(conn, ds, outParameterValues, id,
					new Date());
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: queryLoanStatis
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-26 下午02:58:07
	 * @Return:
	 * @Descb: 查询借款人的还款记录
	 * @Throws:
	 */
	public List<Map<String, Object>> queryRepaymentList(Long userId) throws Exception {
		Connection conn = MySQL.getConnection();
		try {
			return myHomeDao.queryRepaymentList(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return null;
	}

	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: queryRepaymentByOwner
	 * @Param: MyHomeService
	 * @Author:
	 * @Date:
	 * @Return:
	 * @Descb: 查询最近还款日及还款金额
	 * @Throws:
	 */

	public Map<String, String> queryRepaymentByOwner(long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = myHomeDao.queryRepaymentByOwner(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return map;
	}
	
	
	

	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: queryRepaymentByOwner
	 * @Param: MyHomeService
	 * @Author:
	 * @Date:
	 * @Return:
	 * @Descb: 查询最近还款日及还款金额(借款人)
	 * @Throws:
	 */

	public Map<String, String> queryRepaymentByBorrowPer(long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = myHomeDao.queryRepaymentByBorrowPer(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return map;
	}
	
	
	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: queryRepaymentByOwner
	 * @Param: MyHomeService
	 * @Author:
	 * @Date:
	 * @Return:
	 * @Descb: 借款人资金统计利息总额  应还总额
	 * @Throws:
	 */

	public Map<String, String> queryBorrowPerStatisc(long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = myHomeDao.queryBorrowPerStatisc(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return map;
	}
	/**
	 * 查询所有的流水
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> queryFundRecordAll(Long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		try {
			return myHomeDao.queryFundRecordAll(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return null;
	}
	/**
	 * 查询一个月的流水
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> queryFundRecord(Long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		try {
			return myHomeDao.queryFundRecord(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return null;
	}
	
	/**
	 * 查询最近三条流水
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> queryFundRecordThree(Long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		try {
			return myHomeDao.queryFundRecordThree(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return null;
	}
	
	
	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: queryFinanceStatis
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-26 下午02:58:20
	 * @Return:
	 * @Descb: 查询理财统计
	 * @Throws:
	 */
	public Map<String, String> queryFinanceStatis(Long id) throws SQLException,
			DataException {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getFinanceStatis(conn, ds, outParameterValues, id);
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return map;
	}

	/**
	 * @MethodName: queryBorrowInvestByCondition
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-26 下午11:13:56
	 * @Return:
	 * @Descb: 成功投资借款记录
	 * @Throws:
	 */
	public void queryBorrowInvestByCondition(String title,
			String publishTimeStart, String publishTimeEnd,
			String borrowStatus, Long id, PageBean pageBean) throws Exception {
		// modify by houli 2013-04-25 添加了b.isDayThe字段
		String resultFeilds = "a.id,a.borrowId,b.borrower,b.borrowTitle,b.borrowWay,b.paymentMode,"
				+ "b.annualRate,b.deadline,b.publishTime,b.credit,b.creditrating, round(a.investAmount,2) as investAmount,"
				+ "b.schedules,b.times,b.isDayThe ,b.borrowShow ,DATE_FORMAT(a.investTime,'%Y-%m-%d') investTime,b.borrowAmount";
		StringBuffer condition = new StringBuffer();
		if (!"2".equals(borrowStatus)) {
			condition.append(" and ( ( 1=1   ");
		}

		if (StringUtils.isNotBlank(title)) {
			condition.append(" and b.borrowTitle  LIKE CONCAT('%','"
					+ StringEscapeUtils.escapeSql(title.trim()) + "','%')");
		}
		if (StringUtils.isNotBlank(publishTimeStart)) {
			condition.append(" and b.publishTime >'"
					+ StringEscapeUtils.escapeSql(publishTimeStart.trim())
					+ "'");
		}
		if (StringUtils.isNotBlank(publishTimeEnd)) {
			condition.append(" and b.publishTime <'"
					+ StringEscapeUtils.escapeSql(publishTimeEnd.trim()) + "'");
		}
		if (StringUtils.isNotBlank(borrowStatus)) {
			String idStr = StringEscapeUtils
					.escapeSql("'" + borrowStatus + "'");
			String idSQL = "-2";
			idStr = idStr.replaceAll("'", "");
			String[] array = idStr.split(",");
			for (int n = 0; n <= array.length - 1; n++) {
				idSQL += "," + array[n];
			}
			condition.append(" and b.borrowStatus in(" + idSQL + ")");
		}
		if (!"2".equals(borrowStatus)) {
			condition.append(" ) or (b.borrowShow=2) )");
		}
		condition.append("   and a.investor =" + id
				+ " order by b.publishTime desc");
		Connection conn = MySQL.getConnection();
		try {
			dataPage(
					conn,
					pageBean,
					" v_t_invest_borrow a  LEFT JOIN   v_t_invest_borrow_list b  ON a.borrowId=b.id ",
					resultFeilds, "", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * @MethodName: queryBorrowRecycleByCondition
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-27 上午11:57:07
	 * @Return:
	 * @Descb: 查询回收中的借款
	 * @Throws:
	 */
	public void queryBorrowRecycleByCondition(String title, Long id,
			PageBean pageBean) throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(title)) {
			condition.append(" and borrowTitle  LIKE '%"
					+ StringEscapeUtils.escapeSql(title.trim()) + "%'");
		}
		condition.append(" and investor =" + id);
		Connection conn = MySQL.getConnection();
		StringBuffer comm = new StringBuffer();
		try {
			dataPage(conn, pageBean, " v_t_invest_recycling_my ", resultFeilds,
					"", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	public void queryBorrowRecycleByConditionApp(String title,
			long userId, PageBean pageBean) throws SQLException, DataException {
		String resultFeilds = "a.REPAYPERIOD as \"repayPeriod\",date_format(a.REPAYDATE,'%Y-%m-%d') as \"repayDate\",round(a.RECIVEDPRINCIPAL,2) AS \"forpayPrincipal\",round(a.RECIVEDINTEREST,2) AS \"forpayInterest\", round(a.PRINCIPALBALANCE,2) AS \"principalBalance\", " +
				"round(("+IConstants.I_MANAGE+"*a.RECIVEDINTEREST),2) AS \"manage\",a.ISLATE as \"isLate\",a.LATEDAY as \"lateDay\",round(a.RECIVEDFI,2) AS \"forFI\", (a.RECIVEDINTEREST -round(a.RECIVEDINTEREST*"+IConstants.I_MANAGE+",2)+a.RECIVEDFI ) AS \"earn\"," +
				"d.USERNAME as \"username\",a.ISWEBREPAY as \"isWebRepay\",a.REPAYSTATUS as \"repayStatus\",(round(a.RECIVEDINTEREST,2)+round(a.RECIVEDPRINCIPAL,2)) as \"reciveSum\" ";
	
		StringBuffer condition = new StringBuffer();
//		if (StringUtils.isNotBlank(title)) {
//			condition.append(" and b.\"borrowTitle\"  LIKE '%"+StringEscapeUtils.escapeSql(title.trim())+"%'");
//		}
		condition.append(" and a.OWNER =" + userId);
		Connection conn = connectionManager.getConnection();
		try {
			dataPage(conn, pageBean, "T_INVEST_REPAYMENT a LEFT JOIN T_REPAYMENT b on a.REPAYID=b.id LEFT JOIN T_BORROW c on b.borrowId=c.id LEFT JOIN T_USER d on c.PUBLISHER=d.id",
					resultFeilds, " order by a.REPAYDATE ", condition.toString());
			
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
		
	}

	/**
	 * @MethodName: queryBorrowRecycledByCondition
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-27 下午02:09:03
	 * @Return:
	 * @Descb: 查询已回收的借款
	 * @Throws:
	 */
	public void queryBorrowRecycledByCondition(String title, Long id,
			PageBean pageBean) throws Exception {
		String resultFeilds = "t.bid,t.investor,t.borrowId,t.borrower,t.borrowTitle,t.borrowWay,t.credit,t.creditrating,t.annualRate,t.deadline"
				+ " ,t.isDayThe " + // add by houli 区别天标还是月标
				",round(t.realAmount,2) as realAmount,round(t.forTotalSum,2) forTotalSum,t.isDebt ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(title)) {
			condition.append(" and t.borrowTitle  LIKE CONCAT('%','"
					+ StringEscapeUtils.escapeSql(title.trim()) + "','%')");
		}
		condition.append(" and t.investor =" + id
				+ " and IFNULL(t.forTotalSum,0)>0 ");
		Connection conn = MySQL.getConnection();
		try {
			dataPage(
					conn,
					pageBean,
					"  (select *  from v_t_invest_recycled_sum_ a LEFT JOIN v_t_invest_borrow_list b ON a.borrowId = b.id where  b.borrowShow = 1 "
							+ " union all"
							+ " select * from  v_t_invest_flow a left join v_t_invest_borrow_list c on  a.borrowId = c.id where  c.borrowShow = 2 "
							+ " ) t", resultFeilds, "", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * @MethodName: queryBorrowForpayById
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-27 下午06:55:11
	 * @Return:
	 * @Descb: 查询投资人回收中的还款详情
	 * @Throws:
	 */
	public List<Map<String, Object>> queryBorrowForpayById(long id,
			long userId, long iid) throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = myHomeDao.queryBorrowForpayById(conn, id, userId, iid);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}

	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: queryBorrowHaspayById
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-27 下午06:55:36
	 * @Return:
	 * @Descb: 查询投资人已回收的还款详情
	 * @Throws:
	 */
	public List<Map<String, Object>> queryBorrowHaspayById(long idLong,
			long userId, long iid) throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = myHomeDao.queryBorrowHaspayById(conn, idLong, userId, iid);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return list;
	}

	/**
	 * @MethodName: queryBorrowBackAcountByCondition
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-28 上午12:02:11
	 * @Return:
	 * @Descb: 借款回账查询
	 * @Throws:
	 */
	public void queryBorrowBackAcountByCondition(String title,
			String publishTimeStart, String publishTimeEnd, Long id,
			PageBean pageBean) throws Exception {
		String resultFeilds = " a.id as investId,c.username as borrower,b.borrowTitle,b.id as borrowId,b.annualRate,b.deadline,"
				+ " round(a.realAmount,2) as realAmount,round((a.hasPrincipal+a.hasInterest),2) forHasSum,round((a.recivedPrincipal+a.recievedInterest-a.hasPrincipal-a.hasInterest),2) forTotalSum"
				+ ",b.isDayThe,b.borrowWay  ";// add by houli 添加是否为天标标志
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(title)) {
			condition.append(" and b.borrowTitle  LIKE CONCAT('%','"
					+ StringEscapeUtils.escapeSql(title.trim()) + "','%')");
		}
		if (StringUtils.isNotBlank(publishTimeStart)) {
			condition.append(" and b.publishTime >'"
					+ StringEscapeUtils.escapeSql(publishTimeStart.trim())
					+ " 00:00:00'");
		}
		if (StringUtils.isNotBlank(publishTimeEnd)) {
			condition.append(" and b.publishTime <'"
					+ StringEscapeUtils.escapeSql(publishTimeEnd.trim())
					+ " 23:59:59'");
		}
		condition.append(" and a.investor =" + id);
		condition
				.append(" and (a.recivedPrincipal+a.recievedInterest-a.hasPrincipal-a.hasInterest) > 0");
		Connection conn = MySQL.getConnection();
		try {
			dataPage(
					conn,
					pageBean,
					" t_invest a left join t_borrow b on a.borrowId = b.id left join t_user c on b.publisher= c.id  ",
					resultFeilds, "", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * @MethodName: queryAutomaticBid
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-28 下午03:09:33
	 * @Return:
	 * @Descb: 查询用户自动投标设置
	 * @Throws:
	 */
	public Map<String, String> queryAutomaticBid(Long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = myHomeDao.queryAutomaticBid(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}

	/**
	 * @throws DataException
	 * @MethodName: automaticBidSet
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-28 下午04:33:29
	 * @Return:
	 * @Descb: 自动投标状态设置
	 * @Throws:
	 */
	public long automaticBidSet(long status, long userId) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> userMap = new HashMap<String, String>();
		Long result = -1L;
		try {
			result = myHomeDao.automaticBidSet(conn, status, userId);
			if (result <= 0) {
				conn.rollback();
				return -1L;
			} else {
				userMap = userDao.queryUserById(conn, userId);
				if (status == 1) {
					operationLogDao.addOperationLog(conn, "t_automaticbid",
							Convert.strToStr(userMap.get("username"), ""),
							IConstants.UPDATE, Convert.strToStr(userMap
									.get("lastIP"), ""), 0, "开启自动投标", 1);
				} else {
					operationLogDao.addOperationLog(conn, "t_automaticbid",
							Convert.strToStr(userMap.get("username"), ""),
							IConstants.UPDATE, Convert.strToStr(userMap
									.get("lastIP"), ""), 0, "关闭自动投标", 1);
				}
			}

			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return result;
	}

	/**
	 * @throws DataException
	 * @MethodName: automaticBidModify
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-28 下午05:03:37
	 * @Return:
	 * @Descb: 修改自动投标内容
	 * @Throws:
	 */
	public long automaticBidModify(Double bidAmount, Double rateStart,
			Double rateEnd, Double deadlineStart, Double deadlineEnd,
			Double creditStart, Double creditEnd, Double remandAmount, Long id/*,
			String borrowWay*/) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> userMap = new HashMap<String, String>();
		Long result = -1L;
		try {
			Map<String, String> map = myHomeDao.hasAutomaticBid(conn, id);
			if (map != null && map.size() > 0) {
				// 更新内容
				result = myHomeDao.automaticBidUpdate(conn, bidAmount,
						rateStart, rateEnd, deadlineStart, deadlineEnd,
						creditStart, creditEnd, remandAmount, id/*, borrowWay*/);
			} else {
				// 修改内容
				result = myHomeDao.automaticBidAdd(conn, bidAmount, rateStart,
						rateEnd, deadlineStart, deadlineEnd, creditStart,
						creditEnd, remandAmount, id/*, borrowWay*/);
			}
			if (result <= 0) {
				conn.rollback();
				return -1L;
			} else {
				userMap = userDao.queryUserById(conn, id);
				operationLogDao.addOperationLog(conn, "t_automaticbid", Convert
						.strToStr(userMap.get("username"), ""),
						IConstants.UPDATE, Convert.strToStr(userMap
								.get("lastIP"), ""), 0, "设置自动投标", 1);
			}

			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}
		return result;
	}

	/**
	 * @MethodName: queryBackAcountStatis
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-29 下午11:47:49
	 * @Return:
	 * @Descb: 回账统计
	 * @Throws:
	 */
	public Map<String, String> queryBackAcountStatis(long userId,
			String startTime, String endTime, String title) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		title = StringEscapeUtils.escapeSql(title.trim());
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getBackAcountStatis(conn, ds, outParameterValues,
					startTime, endTime, title, userId);
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * @MethodName: queryBorrowForpayById
	 * @Param: MyHomeService
	 * @Author: gang.lv
	 * @Date: 2013-3-27 下午06:55:11
	 * @Return:
	 * @Descb: 查询投资人回收中的还款详情
	 * @Throws:
	 */
	public List<Map<String, Object>> queryBorrowForpayByIdApp(String date, long userId)
			throws SQLException, DataException {
		Connection conn = connectionManager.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = myHomeDao.queryBorrowForpayByIdApp(conn, date, userId);
		} finally {
			conn.close();
		}
		return list;
	}

	/////////// 投资记录
	//成功投资
	public void queryInvestSuccessedRecord(String title,
			String publishTimeStart, String publishTimeEnd, Long id,
			PageBean pageBean, String publisher) throws SQLException,
			DataException {
		StringBuffer condition = new StringBuffer();

		if (StringUtils.isNotBlank(title)) {
			condition.append(" and borrowTitle ='"
					+ StringEscapeUtils.escapeSql(title.trim()) + "'");
		}

		if (StringUtils.isNotBlank(publishTimeStart)) {
			condition.append(" and STR_TO_DATE(investTime,'%Y-%m-%d') >=" +"STR_TO_DATE('"+StringEscapeUtils.escapeSql(publishTimeStart)+"','%Y-%m-%d')");
		}
		if (StringUtils.isNotBlank(publishTimeEnd)) {
			condition.append(" and STR_TO_DATE(investTime,'%Y-%m-%d') <="+"STR_TO_DATE('"+StringEscapeUtils.escapeSql(publishTimeEnd.trim())+"','%Y-%m-%d')");
		}

		if (StringUtils.isNotBlank(publisher)) {
			condition.append(" and username like '%"
					+ StringEscapeUtils.escapeSql(publisher.trim()) + "%'");
		}
		
		condition
				.append("   and investor =" + id + " order by investTime desc");
		Connection conn = connectionManager.getConnection();
		try {

			dataPage(conn, pageBean, " v_t_invest_successed_record", "*", "",condition.toString());

		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	// 投资记录金额统计
	public Map<String, String> queryAmount(long userId, String flag)
			throws SQLException, DataException {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			if (flag.equals("success")) {
				command
						.append("select sum(realAmount) as realAmount,sum(hasPI+notPI) as shouldGetAmount,sum(hasPI) as hasGetAmount from v_t_invest_successed_record where investor="
								+ userId);
			}
			if (flag.equals("current")) {
				command
						.append("select freezeSum from t_user where id="
								+ userId);
			}
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	// 当前投资
	public void queryInvestCurrentRecord(String title,
			String publishTimeStart, String publishTimeEnd, Long id,
			PageBean pageBean, String publisher) throws SQLException,
			DataException {
		StringBuffer condition = new StringBuffer();

		if (StringUtils.isNotBlank(title)) {
			condition.append(" and borrowTitle ='"
					+ StringEscapeUtils.escapeSql(title.trim()) + "'");
		}
		
		if (StringUtils.isNotBlank(publishTimeStart)) {

			condition.append(" and investTime >'"
					+ StringEscapeUtils.escapeSql(publishTimeStart.trim())
					+ "'");
		}
		if (StringUtils.isNotBlank(publishTimeEnd)) {

			condition.append(" and investTime <'"
					+ StringEscapeUtils.escapeSql(publishTimeEnd.trim()) + "'");
		}

		if (StringUtils.isNotBlank(publisher)) {
			condition.append(" and username like '%"
					+ StringEscapeUtils.escapeSql(publisher.trim()) + "%'");
		}
		condition
				.append("   and investor =" + id + " order by investTime desc");
		Connection conn = connectionManager.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_invest_current_record", "*", "",
					condition.toString());

		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	//债权记录
	public void queryDebtRecord(String title,
			String publishTimeStart, String publishTimeEnd, Long id,
			PageBean pageBean, String publisher) throws SQLException,
			DataException {
		StringBuffer condition = new StringBuffer();

		if (StringUtils.isNotBlank(title)) {
			condition.append(" and borrowTitle ='"
					+ StringEscapeUtils.escapeSql(title.trim()) + "'");
		}
		
		
		if (StringUtils.isNotBlank(publishTimeStart)) {
			condition.append(" and STR_TO_DATE(publishTime,'%Y-%m-%d') >=" +"STR_TO_DATE('"+StringEscapeUtils.escapeSql(publishTimeStart)+"','%Y-%m-%d')");
		}
		if (StringUtils.isNotBlank(publishTimeEnd)) {
			condition.append(" and STR_TO_DATE(publishTime,'%Y-%m-%d') <="+"STR_TO_DATE('"+StringEscapeUtils.escapeSql(publishTimeEnd.trim())+"','%Y-%m-%d')");
		}

		if (StringUtils.isNotBlank(publisher)) {
			condition.append(" and publisher like '%"
					+ StringEscapeUtils.escapeSql(publisher.trim()) + "%'");
		}
		
		condition.append("   and auctionerId =" + id + " order by publishTime desc");
		//condition.append(" order by publishTime desc");
		Connection conn = connectionManager.getConnection();
		try {

			dataPage(conn, pageBean, " v_debt_user", "*", "",condition.toString());

		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	/***
	 * 债权转让合同
	 * @param id
	 * @throws Exception 
	 */
	public Map<String,String> debtHeTong(Long id) throws Exception {
		Connection conn = connectionManager.getConnection();
		Map<String,String> map = new HashMap<String, String>();
		try {
			map = myHomeDao.queryDebtHeTong(conn,id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
//	//用户投资信用积分统计
//	@SuppressWarnings("unchecked")
//	public List<Map<String,Object>> queryCreditScores(long id)throws SQLException ,DataException{
//		StringBuffer command = new StringBuffer();
//		Connection conn = MySQL.getConnection();
//		try {
//			command.append("SELECT f_credit_rating (tu.creditrating) AS credit, count(ti.investor) AS creditrating");
//			command.append(" FROM t_invest ti LEFT JOIN t_borrow tb ON ti.borrowId = tb.id LEFT JOIN t_user tu ON tb.publisher = tu.id");
//			command.append(" WHERE investor="+id+" group by 1 ORDER BY 1 desc");
//			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
//			dataSet.tables.get(0).rows.genRowsMap();
//			return dataSet.tables.get(0).rows.rowsMap;
//		} finally {
//			conn.close();
//		}
//	}
	//按风险评定等级统计
	public Map<String,String> queryCreditA()throws SQLException ,DataException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append("SELECT  COUNT(allScore)  as A  from   v_t_borrow_index  WHERE  allScore BETWEEN 400 AND  449  and  sorts != 0 and borrowStatus > 1");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public Map<String,String> queryCreditB()throws SQLException ,DataException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append("SELECT  COUNT(allScore)  as B  from   v_t_borrow_index  WHERE  allScore BETWEEN 300 AND  399  and  sorts != 0 and borrowStatus > 1");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	public Map<String, String> queryCreditC()throws SQLException ,DataException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append("SELECT  COUNT(allScore)  as C  from   v_t_borrow_index  WHERE  allScore BETWEEN 220 AND  299  and  sorts != 0 and borrowStatus > 1");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public Map<String, String> queryCreditD()throws SQLException ,DataException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append("SELECT  COUNT(allScore)  as D  from   v_t_borrow_index  WHERE  allScore BETWEEN 180 AND  219  and  sorts != 0 and borrowStatus > 1");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	public Map<String,String> queryCreditE()throws SQLException ,DataException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append("SELECT  COUNT(allScore)  as E  from   v_t_borrow_index  WHERE  allScore BETWEEN 100 AND  179  and  sorts != 0 and borrowStatus > 1");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	public Map<String,String> queryCreditHR()throws SQLException ,DataException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append("SELECT  COUNT(allScore)  as HR  from   v_t_borrow_index  WHERE  allScore BETWEEN 0 AND  99  and  sorts != 0 and borrowStatus > 1");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	//统计所有的投资记录条数
	public Map<String, String> queryAllInvestCount()throws SQLException ,DataException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append("select count(*) as counts from v_t_borrow_index  where  sorts != 0 and borrowStatus > 1;");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	//回款统计
	//指定月份的每日回款情况
	@SuppressWarnings({ "unchecked", "deprecation" })
	public List<HashMap<String, Object>> queryDayAcount(int year,int month,String id) throws SQLException{
		List<HashMap<String, Object>> list=null;
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		
		try {
//			command.append("select (income) as income,date_format(recordTime,'%Y-%m-%d') as recordTime from t_fundrecord");
//			command.append(" WHERE trader="+id+" ORDER BY 2 asc");
			command.append("select (recivedPrincipal+recivedInterest) income,repayDate as recordTime from t_invest_repayment");
			command.append(" where owner ="+id+" ORDER BY 2 asc;");
			
			
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			list=dataSet.tables.get(0).rows.rowsMap;
			
			Date now=new Date();
			now.setMonth(month-1);
			now.setYear(year-1900);
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(now);
			
			int days=0;
			if(month==2){
			 	Calendar c = Calendar.getInstance();
			    c.set(year, 2, 1);// year年的3月1日
			    c.add(Calendar.DAY_OF_MONTH, -1);//将3月1日往左偏移一天结果是2月的天数
			    days=c.get(Calendar.DAY_OF_MONTH);
			}else{
				days=calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
			}
			List<HashMap<String, Object>> daysAcount=new ArrayList<HashMap<String, Object>>();
			SimpleDateFormat format =new SimpleDateFormat("yyyy-MM-dd");
			for(int i=1;i<=days;i++){
				Map<String, Object> map=new HashMap<String, Object>();
				map.put("day", String.valueOf(i));
				double amount=0;
				if(list!=null&&list.size()!=0){
					now.setMonth(month-1);
					now.setDate(i);
					String flag=format.format(now);
					for(int j=0;j<list.size();j++){
						String data = list.get(j).get("recordTime")+"";
						if(flag.equals(data)){
							if(list.get(j).get("income")==null){
								amount+=0.0;
							}else{
								amount+=Convert.strToDouble(list.get(j).get("income").toString(), 0);
							}
						}
					}
				}
				if(amount!=0){
					map.put("amount",String.format("%.2f", amount));
				}else{
					map.put("amount","");
				}
				daysAcount.add((HashMap<String, Object>) map);
			}
			
			return daysAcount;
		} finally {
			conn.close();
		}
	}
	
	//统计指定月份的回款请款
	public Map<String, String> queryMonthAcount(int month,String id) throws DataException, SQLException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			Date now=new Date();
			now.setMonth(month);
			SimpleDateFormat format=new SimpleDateFormat("yyyy-MM");
			String flag=format.format(now);
			
			command.append("select sum(income) as hasPIMonthAcount,date_format(recordTime,'%Y-%m') as realRepayDate from t_fundrecord");
			command.append(" where YEAR(recordTime)="+String.valueOf(now.getYear()+1900));
			command.append(" and MONTH(recordTime)="+String.valueOf(month));
			command.append(" and trader="+id);
			command.append(" group by 2");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	//统计今年的回款请款
	public Map<String, String>queryYearAcount(String id) throws DataException, SQLException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			Date now=new Date();
			command.append("select sum(income) as hasPIYearAcount,date_format(recordTime,'%Y') as realRepayDate from t_fundrecord");
			command.append(" where YEAR(recordTime)="+String.valueOf(now.getYear()+1900));
			command.append(" and trader="+id);
			command.append(" group by 2");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	

	//统计指定月份的回款请款
	public Map<String, String> queryMonthAcounta(int year,int month,String id) throws DataException, SQLException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			Date now=new Date();
//			now.setMonth(month);
			SimpleDateFormat format=new SimpleDateFormat("yyyy-MM");
			String flag=format.format(now);
			
//			command.append("select sum(income) as hasPIMonthAcount,date_format(recordTime,'%Y-%m') as realRepayDate from t_fundrecord");
//			command.append(" where YEAR(recordTime)="+String.valueOf(now.getYear()+1900));
//			command.append(" and MONTH(recordTime)="+String.valueOf(month));
//			command.append(" and trader="+id);
//			command.append(" group by 2");
			
//			 WHERE YEAR(repayDate)=2013 AND MONTH(repayDate)=12 AND invest_id=7 GROUP BY 2

			command.append("SELECT FORMAT(SUM(recivedPrincipal+recivedInterest),2) AS hasPIMonthAcount,DATE_FORMAT(repayDate,'%Y-%m') AS realRepayDate FROM t_invest_repayment");
			command.append(" WHERE YEAR(repayDate)="+String.valueOf(year));
			command.append(" AND MONTH(repayDate)="+String.valueOf(month));
			command.append(" AND owner="+id);
			command.append(" group by 2");
			
			
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	//统计今年的回款请款
	public Map<String, String>queryYearAcounta(int year,String id) throws DataException, SQLException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			Date now=new Date();
//			command.append("select sum(income) as hasPIYearAcount,date_format(recordTime,'%Y') as realRepayDate from t_fundrecord");
//			command.append(" where YEAR(recordTime)="+String.valueOf(now.getYear()+1900));
//			command.append(" and trader="+id);
//			command.append(" group by 2");
			command.append("SELECT FORMAT(SUM(recivedPrincipal+recivedInterest),2) AS hasPIYearAcount,DATE_FORMAT(repayDate,'%Y') AS realRepayDate FROM t_invest_repayment");
			command.append(" WHERE YEAR(repayDate)="+String.valueOf(year));
			command.append(" AND owner="+id);
			command.append(" group by 2");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * 会员积分
	 * @param investor
	 * @return
	 * @throws SQLException
	 * @throws DataException
	 */
	public Map<String,String> queryUserCredits(Object  investor)throws SQLException ,DataException{
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" select userCredit as userCredits from t_user  where id =" +investor);
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	/**
	 * 个人头像
	 * @param useId
	 * @return
	 * @throws DataException
	 * @throws SQLException
	 */
	public  Map<String , String >  queryPersonalHead(long useId) throws DataException, SQLException
	{
		StringBuffer  sb = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			sb.append("select imgPath as  imgPath  from t_borrow where  publisher=" +useId);
			DataSet dataSet =MySQL.executeQuery(conn, sb.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			
			conn.close();
		}
	}
	
	/**
	 * 查询新手充值红包
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public Map<String, String> queryRechargeFundRecord(Long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		try {
			return myHomeDao.queryRechargeFundRecord(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return null;
	}
	
	// 按编号查询转让信息（债权协议）
	public List<Map<String, Object>> queryLoanAssignment(String ids) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" SELECT a.id, round(a.investAmount,2) as investAmount, tp.idNo AS investorIdNo, a.borrowId, a.auditTime ");
			command.append(" ,a.tradeValue, a.soldPrice, b.deadline, (b.deadline - b.hasDeadline) as redeemDeadline, b.realName AS borrower, b.borrowerIdNo ");
			command.append(" FROM v_t_can_assignment_invest a LEFT JOIN v_t_borrow_detail b ON a.borrowId = b.id ");
			command.append(" LEFT JOIN t_person tp ON a.investor = tp.userId WHERE a.id in (" + ids + ") ");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			command = null;
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	//查询是否已增加体验金
	public Map<String, String> queryIsRechargeTyj(Long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		try {
			return myHomeDao.queryIsRechargeTyj(conn, userId);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return null;
	}
	
	public void queryUserReffereeInfo(String refferee,PageBean pageBean) throws SQLException,DataException {
		StringBuffer condition = new StringBuffer();
		condition.append(" and refferee = '" + refferee + "' ");
		Connection conn = connectionManager.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_user_refferee_info ", " * ", " order by createTime desc ",
					condition.toString());
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	public void queryUserCapitalGuard(String username, String capitalLevel,
			String refferee, PageBean pageBean) throws SQLException,
			DataException {
		StringBuffer condition = new StringBuffer();
		condition.append(" and refferee = '" + refferee + "' ");
		
		if(StringUtils.isBlank(username)){
			condition.append(" and usableSum > 100 and zgDays > 7 ");
		}else{
			condition.append(" and username = '" + username + "' ");
		}
		
		if(!capitalLevel.equals("")){
			condition.append(" and zjdj = '" + capitalLevel + "' ");
		}
		
		Connection conn = connectionManager.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_user_refferee_info ", " * ",
					" order by zjdj,zgDays desc ", condition.toString());
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	public Map<String , String > queryUserReffereeCounts(String refferee) throws DataException, SQLException
	{
		StringBuffer  sb = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			sb.append("select count(id) as counts from v_t_user_refferee_info where refferee='" + refferee + "'");
			DataSet dataSet =MySQL.executeQuery(conn, sb.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public Map<String , String > queryUserReffereeCapital(Long userId) throws DataException, SQLException
	{
		StringBuffer  sb = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			sb.append(" SELECT round(sum(investAmount),2) as amountSum ");
			sb.append(" from t_gofull_award_history ");
			sb.append(" where userId =  " + userId);
			sb.append(" group by userId,to_days(liquidationTime) ");
			sb.append(" order by to_days(liquidationTime) desc limit 0,1 ");
			DataSet dataSet =MySQL.executeQuery(conn, sb.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public List<Map<String, String>> reffereeCapital15days(long userId) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" SELECT userId , sum(investAmount) AS drzk , DATE_FORMAT(liquidationTime, '%m.%d') AS rq ");
			command.append(" FROM t_gofull_award_history ");
			command.append(" WHERE userId = " + userId);
			command.append(" AND datediff(curdate(), liquidationTime) < 15 ");
			command.append(" GROUP BY userId, to_days(liquidationTime) LIMIT 0,15;");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			command = null;
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	public long applyToRefferee(Long userId) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> userMap = new HashMap<String, String>();
		Long result = -1L;
		try {
			result = myHomeDao.applyToRefferee(conn, userId);
			if (result <= 0) {
				conn.rollback();
				return -1L;
			} 
			
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}
		return result;
	}
	
	public Map<String , String > queryAvailableElite(long userId) throws DataException, SQLException
	{
		StringBuffer  sb = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			sb.append("SELECT ifnull(sum(amount), 0) AS amount FROM t_elite WHERE status = 0 AND userId = " + userId);
			DataSet dataSet =MySQL.executeQuery(conn, sb.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public Map<String , String > queryTdEliteInterest(long userId) throws DataException, SQLException
	{
		StringBuffer  sb = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			sb.append("SELECT ifnull(sum(recievedInterest), 0) AS interest FROM t_elite WHERE to_days(repayDate) = to_days(curdate()) AND userId = " + userId);
			DataSet dataSet =MySQL.executeQuery(conn, sb.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public Map<String , String > queryExpectEliteInterest(long userId) throws DataException, SQLException
	{
		StringBuffer  sb = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			sb.append("SELECT ifnull(sum(recievedInterest), 0) AS expectInterest FROM t_elite WHERE interestStatus = 1 AND userId = " + userId);
			DataSet dataSet =MySQL.executeQuery(conn, sb.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public Map<String , String > queryLastEliteProduct() throws DataException, SQLException
	{
		StringBuffer  sb = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			sb.append("SELECT * FROM t_elite_product WHERE status = 1  ");
			DataSet dataSet =MySQL.executeQuery(conn, sb.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	public List<Map<String, String>> queryEliteRecord(long userId) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" SELECT date_format(addTime,'%Y.%m.%d') as addTime, amount,getType, ");
			command.append(" remarks,date_format(deadTime,'%Y.%m.%d') as deadTime,status       ");
			command.append(" FROM t_elite ");
			command.append(" WHERE userId = " + userId);
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			command = null;
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	public List<Map<String, String>> queryEliteInvestRecord(long userId) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" SELECT date_format(a.investTime,'%Y.%m.%d') as investTime,       ");
			command.append(" a.amount,b.productName,round(a.recievedInterest,2) as recievedInterest,interestStatus, ");
			command.append(" date_format(a.getInterestDate,'%Y.%m.%d') as getInterestDate,     ");
			command.append(" date_format(a.repayDate,'%Y.%m.%d') as repayDate     ");
			command.append(" FROM t_elite a  left join t_elite_product b on a.productId = b.id ");
			command.append(" WHERE a.userId = " + userId);
			command.append(" AND a.status = 1 ");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			command = null;
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	public List<Map<String, String>> queryAvailableEliteList(long userId) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" SELECT * ");
			command.append(" FROM t_elite a ");
			command.append(" WHERE a.userId = " + userId);
			command.append(" AND a.status = 0 ");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			command = null;
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	
	public void queryAllVoucherList(long userId,PageBean pageBean) throws Exception {
		StringBuffer condition = new StringBuffer();
		condition.append(" and userId =" + userId + " order by  usableStatus asc,deadTime asc ");
		Connection conn = connectionManager.getConnection();
		try {
			dataPage(
					conn,
					pageBean,
					" t_gofull_voucher ",
					" *, date_format(createTime, '%Y.%m.%d') AS createTimeFormat, date_format(deadTime, '%Y.%m.%d') AS deadTimeFormat ",
					"", condition.toString());
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} catch (DataException e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	public List<Map<String, Object>> getVoucherRecordsById(long userId,long voucherId) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" SELECT *,date_format(a.exchangeTime,'%Y.%m.%d') as exchangeTimeFormat ");
			command.append(" FROM t_gofull_voucher_record a ");
			command.append(" WHERE a.voucherId = " + voucherId);
			command.append(" AND a.userId = " + userId);
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			command = null;
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	public Map<String , String > queryAvailableVoucherCounts(long userId) throws DataException, SQLException
	{
		StringBuffer  sb = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			sb.append(" SELECT count(id) as num from t_gofull_voucher WHERE usableStatus = 1 AND userId = " + userId);
			DataSet dataSet =MySQL.executeQuery(conn, sb.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
	
	
	/**
	 * @Return:
	 * @Descb: 体验金投资
	 * @Throws:
	 */
	public Map<String, String> addEliteInvest(long userId
			) throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_elite_invest(conn, ds, outParameterValues, userId,0, "");
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			if (ret <= 0) {
				conn.rollback();
			} else {
			}
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * @Return:
	 * @Descb: 体验金收益领取
	 * @Throws:
	 */
	public Map<String, String> eliteReceiveAll(long userId
			) throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_elite_receive(conn, ds, outParameterValues, userId,0, "");
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			if (ret <= 0) {
				conn.rollback();
			} else {
			}
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * @Return:
	 * @Descb: 兑换码兑换共富券
	 * @Throws:
	 */
	public Map<String, String> redeemVoucherCode(long userId,String code) throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_redemptionCode_to_voucher(conn, ds, outParameterValues, userId,code, 0,
					"");
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			if (ret <= 0) {
				conn.rollback();
			} else {
			}
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * @MethodName: getMyRank
	 * @Param: MyHomeService
	 * @Author: wujc
	 * @Date: 2015-07-22
	 * @Return:
	 * @Descb: 足迹-等级 魅力指数 红颜指数 跟投指数
	 * @Throws:
	 */
	public Map<String, String> getMyRank(long userId) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.p_get_rank(conn, ds, outParameterValues,userId,0,"");
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * @MethodName: getTimeLine
	 * @Param: MyHomeService
	 * @Author: wujc
	 * @Date: 2015-07-28
	 * @Return:
	 * @Descb: 足迹-时光轴
	 * @Throws:
	 */
	public List<Map<String, Object>> getTimeLine(long userId) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			command.append(" SELECT tu.createTime AS dt ,'" + "注册共富网" + "' AS remarks FROM t_user tu WHERE tu.id = " + userId + " ");
			command.append(" UNION ALL ");
			command.append(" SELECT MIN(tf.recordTime) AS dt ,'" + "第一次充值" + "' AS remarks FROM t_fundrecord tf  WHERE tf.userId = " + userId + " AND tf.operateType <= 2 ");
			command.append(" UNION ALL ");
			command.append(" SELECT MIN(ti.investTime) AS dt ,'" + "第一次投资过万" + "' AS remarks FROM t_invest ti WHERE ti.investor = " + userId + " AND ti.investAmount > 10000 ");
			command.append(" UNION ALL ");
			command.append(" SELECT MIN(tf.recordTime) AS dt ,'" + "第一次回款" + "' AS remarks FROM t_fundrecord tf WHERE tf.userId = " + userId + " AND tf.operateType = 151 ");
			command.append(" UNION ALL ");
			command.append(" SELECT MIN(tu.createTime) AS dt ,'" + "第一次推荐好友加入共富网" + "' AS remarks FROM t_user tu WHERE tu.refferee = " +
					"(SELECT username FROM t_user tu1 WHERE tu1.id =  " + userId + "  ) AND tu.id <>  " + userId + "  ORDER BY dt DESC");
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			dataSet.tables.get(0).rows.genRowsMap();
			command = null;
			return dataSet.tables.get(0).rows.rowsMap;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @Return:
	 * @Descb: 每日签到
	 * @Throws:
	 */
	public Map<String, String> signToday(long userId) throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_user_sign(conn, ds, outParameterValues, userId, 0,
					"");
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			if (ret <= 0) {
				conn.rollback();
			} else {
			}
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * @Return:
	 * @Descb: 签到记录
	 * @Throws:
	 */
	public Map<String , String > queryMySignRecord(long userId) throws DataException, SQLException
	{
		StringBuffer  sb = new StringBuffer();
		Connection conn = MySQL.getConnection();
		try {
			sb.append(" SELECT ts.signCount                                      ");
			sb.append("      , datediff(now(),ts.lastModifyTime) AS isSignToday ");
			sb.append(" FROM                                                     ");
			sb.append("   t_sign ts                                              ");
			sb.append(" WHERE                                                    ");
			sb.append("   ts.userId = " + userId );
			DataSet dataSet =MySQL.executeQuery(conn, sb.toString());
			return  BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
	}
}